package org.yoao.crm.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.yoao.crm.DBManager.DBManager;
import org.yoao.crm.bean.SignupBean;
import org.yoao.crm.tools.DaoUtil;

/**
 * @author yxwonline.com
 * @description
 * @date 2010-8-13 下午07:08:44
 */
public class SignupDAO extends DBManager {
	private static final Logger log = Logger.getLogger(SignupDAO.class);

	public int insert(SignupBean bean) throws Exception {
		String sql = "insert into " + bean.getTableName()
				+ "([name], phone, email, address, content, bmtime, "
				+ "isFlag, memo, userSno) values("
				+ DaoUtil.setValue(bean.getName()) + ", "
				+ DaoUtil.setValue(bean.getSj()) + ", "
				+ DaoUtil.setValue(bean.getEmail()) + ", "
				+ DaoUtil.setValue(bean.getAddress()) + ", "
				+ DaoUtil.setValue(bean.getIntro()) + ", "
				+ DaoUtil.setValue(bean.getTime()) + ", 0, NULL, NULL)";
		return update(sql);
	}

	public int modifyMemo(SignupBean bean) throws Exception {
		String sql = "update " + bean.getTableName() + " set memo='"
				+ bean.getMemo() + "' where id=" + bean.getId();
		return update(sql);
	}
	
	public String findMemo(String tableName, String id) throws Exception {
		String sql = "select memo from " + tableName + " where id=" + id;
		return querySingleValue(sql);
	}

	public int modifyIsFlag(String tableName, String id, String isFlag)
			throws Exception {
		String sql = "update " + tableName + " set isFlag=" + isFlag
				+ " where id in(" + id + ")";
		return update(sql);
	}

	public int delete(String id, String tableName) throws Exception {
		String sql = "delete " + tableName + " where id in(" + id + ")";
		return update(sql);
	}

	public int getDataCount(Map map) throws Exception {
		String tableName = (String) map.get("TABLENAME");
		String sql = "select count(id) as signupCount from " + tableName;
		String where = " where ";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				if (key.equals("TABLENAME"))
					continue;
				String value = map.get(key).toString();
				if (key.indexOf(" LIKE ") < 1) {
					where = where + key + "='" + value + "' and ";
				} else {
					where = where + key + " '%" + value + "%' and ";
				}
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(" where "))
			where = "";
		sql += where;
		String result = querySingleValue(sql);
		if (result == null || result.trim().equals(""))
			result = "0";
		return Integer.parseInt(result);
	}

	public List find(int startRow, int endRow, Map map) throws Exception {
		String tableName = (String) map.get("TABLENAME");
		String sql = "select top " + endRow + " " + tableName + ".id, "
				+ tableName + ".[name], " + tableName + ".phone, " + tableName
				+ ".email, " + tableName + ".address, " + tableName
				+ ".bmtime, " + tableName + ".content, " + tableName
				+ ".isFlag, " + tableName
				+ ".memo, baseUser.nickname as userSno from " + tableName
				+ " left join baseuser on baseuser.userName=" + tableName
				+ ".userSno " + " where " + tableName + ".id not in(";
		String filterSql = "select top " + startRow + " s2.id from "
				+ tableName + " as s2 ";

		String where = "";
		String fwhere = "";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				if (key.equals("TABLENAME"))
					continue;
				String value = map.get(key).toString();
				if (key.indexOf(" LIKE ") < 1) {
					where = where + tableName + "." + key + "='" + value
							+ "' and ";
					fwhere = fwhere + "s2." + key + "='" + value + "' and ";
				} else {
					where = where + tableName + "." + key + " '%" + value
							+ "%' and ";
					fwhere = fwhere + "s2." + key + " '%" + value + "%' and ";
				}
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(""))
			where = null;
		if (fwhere.endsWith(" and "))
			fwhere = fwhere.substring(0, fwhere.length() - 4);
		if (fwhere.equals(""))
			fwhere = null;

		if (where == null) {
			sql = sql + filterSql + " order by s2.id desc) order by "
					+ tableName + ".id desc";
		} else {
			filterSql = filterSql + " where " + fwhere;
			sql = sql + filterSql + " order by s2.id desc) and " + where
					+ " order by " + tableName + ".id desc";
		}
		return query(sql);
	}

	private List query(String sql) {
		log.debug(sql);
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		List result = new ArrayList();
		try {
			con = getConnection();
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			String columnName = null;
			while (rs.next()) {
				SignupBean bean = new SignupBean();
				for (int i = 1; i <= columnCount; i++) {
					columnName = rsmd.getColumnName(i);
					if (columnName.equalsIgnoreCase("ID"))
						bean.setId(rs.getInt(columnName));
					if (columnName.equalsIgnoreCase("NAME"))
						bean.setName(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("PHONE"))
						bean.setSj(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("EMAIL"))
						bean.setEmail(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("ADDRESS"))
						bean.setAddress(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("BMTIME"))
						bean.setTime(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("CONTENT"))
						bean.setIntro(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("ISFLAG"))
						bean.setIsFlag(rs.getInt(columnName));
					if (columnName.equalsIgnoreCase("MEMO"))
						bean.setMemo(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("USERSNO"))
						bean.setUserName(rs.getString(columnName));
				}
				result.add(bean);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			result = null;
			log.error("ERROR SQL:" + sql);
			e.printStackTrace();
		} finally {
			closeConnection(con);
			return result;
		}
	}

	public synchronized String create() {
		try {
			log.info("开始初始化表signup...");
			String sql = "SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[signup]');";
			List list = this.queryMultiColumn(sql);
			if (list == null) {
				log
						.warn("系统检测失败。\n原因:\n  1.数据库非正常运行;\n  2.帐户没权限不足;\n  3.数据库连接异常。");
				return "系统检测失败。\n原因:\n  1.数据库非正常运行;\n  2.帐户没权限不足;\n  3.数据库连接异常。";
			}
			if (list.isEmpty()) {
				log.info("表baseuser不存在，开始创建...");
				sql = "/****** 对象:  Table [dbo].[signup]    脚本日期: 06/24/2009 17:40:16 ******/ "
						+ "SET ANSI_NULLS ON;"
						+ "SET QUOTED_IDENTIFIER ON;"
						+ "SET ANSI_PADDING ON;"
						+ "CREATE TABLE [dbo].[baseuser]("
						+ "[id] [int] IDENTITY(1,1) NOT NULL,"
						+ "[name] [varchar](50) NULL,"
						+ "[phone] [varchar](50) NULL,"
						+ "[email] [varchar](50) NULL,"
						+ "[address] [varchar](50) NULL,"
						+ "[content] [varchar](200) NULL,"
						+ "[bmtime] [varchar](20) NULL,"
						+ "[isFlag] [int]NULL,"
						+ "[memo] [varchar](200) NULL,"
						+ "CONSTRAINT [PK_signup] PRIMARY KEY CLUSTERED"
						+ "("
						+ "[id] ASC"
						+ ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]"
						+ ") ON [PRIMARY];" + "SET ANSI_PADDING OFF;";

				if (update(sql) < 0) {
					log.warn("表signup初始化失败!");
					return "表signup初始化失败!";
				}
				log.info("表signup创建成功!");
				return "表signup创建成功!";
			} else {
				log.info("表signup已存在!");
				return "表signup已存在!";
			}
		} catch (Exception e) {
			log.warn("表signup初始化失败!");
			e.printStackTrace();
			return "表signup初始化失败!" + e.toString();
		}
	}
}